# Author:keith
# -*-  codeing = utf-8 -*-
# @Time :2025/9/15 16:13
# @Author:17986
# @Site  :
# @File  :PdfTables.py
# @Software PyCharm


# import pdfplumber
# file_path = r'E:\桌面文件\国网项目\pdf文件\电子商务平台数据资源手册.PDF'
#
# with pdfplumber.open(file_path) as pdf:
#     page = pdf.pages[28]
#     print(page.extract_tables())

# 第二种 将数据存储在不同的sheet页中
# import camelot
# import pandas as pd
# import os
# from openpyxl import Workbook
# from openpyxl.utils.dataframe import dataframe_to_rows
# import warnings
#
# warnings.filterwarnings('ignore')
#
#
# def extract_tables_to_excel_camelot(pdf_path, excel_path=None, flavor='lattice'):
#     """
#     使用camelot提取PDF表格并保存到Excel
#
#     Args:
#         pdf_path (str): PDF文件路径
#         excel_path (str): 输出的Excel文件路径
#         flavor (str): 提取模式 'lattice'(有线框) 或 'stream'(无线框)
#
#     Returns:
#         str: 生成的Excel文件路径
#     """
#     if excel_path is None:
#         excel_path = os.path.splitext(pdf_path)[0] + "_tables.xlsx"
#
#     try:
#         print(f"开始提取PDF表格: {os.path.basename(pdf_path)}")
#
#         # 提取表格
#         tables = camelot.read_pdf(pdf_path, flavor=flavor, pages='all')
#
#         print(f"共找到 {tables.n} 个表格")
#
#         if tables.n == 0:
#             print("未找到任何表格")
#             return None
#
#         # 创建Excel工作簿
#         with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
#             for i, table in enumerate(tables):
#                 # 获取表格数据
#                 df = table.df
#                 sheet_name = f"Table_{i + 1}"
#
#                 # 处理过长的sheet名称
#                 if len(sheet_name) > 31:
#                     sheet_name = sheet_name[:31]
#
#                 # 将表格写入Excel
#                 df.to_excel(writer, sheet_name=sheet_name, index=False)
#
#                 # 获取工作表对象进行格式调整
#                 worksheet = writer.sheets[sheet_name]
#
#                 # 自动调整列宽
#                 for column in worksheet.columns:
#                     max_length = 0
#                     column_letter = column[0].column_letter
#                     for cell in column:
#                         try:
#                             if len(str(cell.value)) > max_length:
#                                 max_length = len(str(cell.value))
#                         except:
#                             pass
#                     adjusted_width = min(max_length + 2, 50)
#                     worksheet.column_dimensions[column_letter].width = adjusted_width
#
#                 print(f"表格 {i + 1}: 形状 {df.shape}, 准确率: {table.parsing_report['accuracy']:.2f}%")
#
#         print(f"成功保存Excel文件: {excel_path}")
#         return excel_path
#
#     except Exception as e:
#         print(f"提取过程中发生错误: {e}")
#         return None
#
#
# # 使用示例
# pdf_file = "E:\桌面文件\国网项目\pdf文件\财务管控系统（集中部署）数据资源手册.pdf"
# # 如果lattice模式效果不好，可以尝试stream模式
# # excel_file = extract_tables_to_excel_camelot(pdf_file, flavor='stream')
# excel_file = extract_tables_to_excel_camelot(pdf_file)


## 第三种，将全部数据存储在一个sheet页中
import camelot
import pandas as pd
import os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import warnings

warnings.filterwarnings('ignore')


def extract_tables_to_excel_single_sheet(pdf_path, excel_path=None, flavor='lattice'):
    """
    使用camelot提取PDF表格并保存到Excel的同一个sheet中

    Args:
        pdf_path (str): PDF文件路径
        excel_path (str): 输出的Excel文件路径
        flavor (str): 提取模式 'lattice'(有线框) 或 'stream'(无线框)

    Returns:
        str: 生成的Excel文件路径
    """
    if excel_path is None:
        excel_path = os.path.splitext(pdf_path)[0] + "_tables_single_sheet.xlsx"

    try:
        print(f"开始提取PDF表格: {os.path.basename(pdf_path)}")

        # 提取表格
        tables = camelot.read_pdf(pdf_path, flavor=flavor, pages='all')

        print(f"共找到 {tables.n} 个表格")

        if tables.n == 0:
            print("未找到任何表格")
            return None

        # 创建Excel工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "所有表格"

        current_row = 1  # 当前写入的行号

        for i, table in enumerate(tables):
            # 获取表格数据
            df = table.df

            # 添加表格标题
            title_cell = ws.cell(row=current_row, column=1)
            title_cell.value = f"表格 {i + 1} (页码: {table.page}, 准确率: {table.parsing_report['accuracy']:.2f}%)"
            title_cell.font = Font(bold=True, size=12, color="FFFFFF")
            title_cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")

            # 合并标题单元格（跨所有列）
            ws.merge_cells(start_row=current_row, start_column=1, end_row=current_row, end_column=len(df.columns))
            current_row += 1
            current_row += 1  # 空一行

            # 写入表头
            for col_idx, column_name in enumerate(df.columns, 1):
                header_cell = ws.cell(row=current_row, column=col_idx)
                header_cell.value = column_name
                header_cell.font = Font(bold=True, color="FFFFFF")
                header_cell.fill = PatternFill(start_color="5B9BD5", end_color="5B9BD5", fill_type="solid")
                header_cell.alignment = Alignment(horizontal='center', vertical='center')

            # 写入表格数据
            for row_idx in range(len(df)):
                for col_idx in range(len(df.columns)):
                    cell = ws.cell(row=current_row + row_idx + 1, column=col_idx + 1)
                    cell.value = df.iloc[row_idx, col_idx]
                    cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)

            current_row += len(df) + 1  # 移动到下一个表格的位置（数据行数 + 表头）
            current_row += 2  # 表格之间空两行

            print(f"表格 {i + 1}: 形状 {df.shape}, 准确率: {table.parsing_report['accuracy']:.2f}%")

        # 自动调整所有列的宽度
        for col_idx in range(1, ws.max_column + 1):
            max_length = 0
            col_letter = get_column_letter(col_idx)
            for row in range(1, ws.max_row + 1):
                cell_value = ws.cell(row=row, column=col_idx).value
                if cell_value:
                    max_length = max(max_length, len(str(cell_value)))
            adjusted_width = min(max_length + 2, 50)
            ws.column_dimensions[col_letter].width = adjusted_width

        # 保存Excel文件
        wb.save(excel_path)
        print(f"成功保存Excel文件: {excel_path}")
        print(f"所有表格已保存在同一个sheet中，共 {tables.n} 个表格")
        return excel_path

    except Exception as e:
        print(f"提取过程中发生错误: {e}")
        return None


# 使用示例
pdf_file = r"E:\桌面文件\国网项目\pdf文件\ERP集中部署数据资源手册-上.pdf"
excel_file = extract_tables_to_excel_single_sheet(pdf_file)

# 如果lattice模式效果不好，可以尝试stream模式
# excel_file = extract_tables_to_excel_single_sheet(pdf_file, flavor='stream')